﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Configuration;
using MySql.Data.MySqlClient;

namespace TMS
{
    public partial class CreateEditOfferControl : UserControl
    {
        Categories cat = new Categories();
        DataSet table = new DataSet();
        Product prod = new Product();
        Decimal NomPrice=0;
        Int32 ProductCounter = 0;
        Offers NewOffer = new Offers();
        Users ActiveUser = Program.GetActiveUser;
        Users users = new Users();
        bool New = false;
        bool Owner = false;

        public CreateEditOfferControl()
        {
            InitializeComponent();
        }

        public CreateEditOfferControl(DataGridViewRow row)
        {
            InitializeComponent();
            dgvProductsList.ColumnCount = 9;
            dgvProductsList.Columns[0].HeaderText = "Бр.";
            dgvProductsList.Columns[1].HeaderText = "Бр.на Пр.";
            dgvProductsList.Columns[2].HeaderText = "Име";
            dgvProductsList.Columns[3].HeaderText = "Опис";
            dgvProductsList.Columns[4].HeaderText = "Ед.М.";
            dgvProductsList.Columns[5].HeaderText = "Цена";
            dgvProductsList.Columns[6].HeaderText = "Количина";
            dgvProductsList.Columns[7].HeaderText = "Вкупно";
            dgvProductsList.Columns[8].Visible = false;


            dgvProductsList.Columns[0].ReadOnly = true;
            dgvProductsList.Columns[1].ReadOnly = true;
            dgvProductsList.Columns[2].ReadOnly = true;
            dgvProductsList.Columns[4].ReadOnly = true;
            dgvProductsList.Columns[5].ReadOnly = true;
            dgvProductsList.Columns[6].ReadOnly = true;
            dgvProductsList.Columns[7].ReadOnly = true;
            dgvProductsList.Columns[8].ReadOnly = true;

            dgvProductsList.Columns[0].Width = 30;
            dgvProductsList.Columns[1].Width = 80;
            dgvProductsList.DefaultCellStyle.WrapMode = DataGridViewTriState.True;
            FillResponsibels();

            if (row.Cells.Count == 3)
            {
                NewOffer.Projcet_ID = Convert.ToInt32(row.Cells[0].Value.ToString());
                NewOffer.ProjectName = row.Cells[1].Value.ToString();
                NewOffer.PartnerName = row.Cells[2].Value.ToString();
                NewOffer.OfferNo = NewOffer.GetLastOffer() + 1;
                Program.GetMainForm.Text = "TMS - Понуда бр." + NewOffer.OfferNo.ToString();
                txtPartner.Text = NewOffer.PartnerName;
                cmbResponsible.SelectedItem = ActiveUser.GetName() + " " + ActiveUser.GetSurname();
                DateTime today = DateTime.Today;
                NewOffer.Date = today.ToString("dd/MM/yyyy");
                New = true;
                Owner = true;
            }
            else
            {
                Program.GetMainForm.Text = "TMS - Понуда бр." + row.Cells[8].Value.ToString();
                FillOldOffer(row);
                NewOffer.ID = Convert.ToInt32(row.Cells[0].Value);
                NewOffer.Projcet_ID = Convert.ToInt32(row.Cells[1].Value);
                NewOffer.ProjectName= row.Cells[9].Value.ToString();
                NewOffer.PartnerName = row.Cells[10].Value.ToString();
                NewOffer.OfferNo = Convert.ToInt32(row.Cells[8].Value);
                NewOffer.Date = row.Cells[2].Value.ToString();
                NewOffer.Note = row.Cells[18].Value.ToString();
                txtPartner.Text = NewOffer.GetPartner(Convert.ToInt32(row.Cells[8].Value));
                txtDescription.Text = row.Cells[11].Value.ToString();
                txtDeliveryDay.Text = row.Cells[12].Value.ToString();
                txtValid.Text = row.Cells[13].Value.ToString();
                txtPaymentMethod.Text = row.Cells[14].Value.ToString();
                txtGuarante.Text = row.Cells[15].Value.ToString();
                txtRequest.Text = row.Cells[16].Value.ToString();
                txtNote.Text = NewOffer.Note;
                cmbResponsible.SelectedItem = row.Cells[17].Value.ToString();
                if ((ActiveUser.GetName() + " " + ActiveUser.GetSurname()).ToString() != cmbResponsible.SelectedItem.ToString()||(row.Cells[4].Value.ToString()!="Пратена"))
                {
                    grDescription.Enabled = false;
                    grConditions.Enabled = false;
                    btnEditRemove.Enabled = false;
                    btnAdd.Enabled = false;
                    dgvProductsList.Columns[2].ReadOnly = true;
                }
                else Owner = true;
                
                New = false;
            }
        }

        private void OfferControl_Load(object sender, EventArgs e)
        {
            cmbCategory1.DisplayMember = "name";
            cmbCategory1.ValueMember = "id";
            cmbCategory2.DisplayMember = "name";
            cmbCategory2.ValueMember = "id";
            cmbCategory3.DisplayMember = "name";
            cmbCategory3.ValueMember = "id";
        }

        private bool Add()
        {
            object[] product = new object[9];
            ProductCounter = dgvProductsList.Rows.Count + 1;
            
            Int64 quantity = Convert.ToInt64(txtQuantity.Text);
            DataSet table = new DataSet();

            foreach (DataGridViewRow row in dgvProductsList.Rows)
                if ((row.Cells[2].Value.ToString() == cmbProductList.Text.ToString()) )
                {
                    MessageBox.Show("Производот веќе се наоѓа во листата\nМожете да ја промените количината");
                    row.Selected = true;
                    dgvProductsList.FirstDisplayedScrollingRowIndex = row.Index;
                    return false;
                }

            table = prod.GetProductFromDBAsDataSet(Convert.ToInt32(cmbProductList.SelectedValue));
            product[0] = ProductCounter;
            product[1] = table.Tables[0].Rows[0].ItemArray[5];
            product[2] = table.Tables[0].Rows[0].ItemArray[4].ToString();
            product[3] = table.Tables[0].Rows[0].ItemArray[10];
            product[4] = table.Tables[0].Rows[0].ItemArray[11];
            product[5] = txtPrice.Text;
            product[6] = quantity;
            product[7] = Convert.ToDecimal(txtPrice.Text) * quantity;
            product[8] = table.Tables[0].Rows[0].ItemArray[0];

            Color RowColor = new Color();
            if (quantity > prod.GetProductQuantityFromWarehouse(Convert.ToInt32(table.Tables[0].Rows[0].ItemArray[0])))
                RowColor = Color.Red;
            else
                RowColor = Color.Black;

            if (dgvProductsList.SelectedRows.Count == 0)
            {
                dgvProductsList.Rows.Add(product);
                dgvProductsList.Rows[dgvProductsList.RowCount - 1].DefaultCellStyle.ForeColor = RowColor;
            }
            else
            {
                dgvProductsList.Rows.Insert(dgvProductsList.SelectedRows[0].Index, product);
                dgvProductsList.Rows[dgvProductsList.SelectedRows[0].Index].DefaultCellStyle.ForeColor = RowColor;
            }
            
           

            Int32 i = 1;
            foreach (DataGridViewRow row in dgvProductsList.Rows)
            {
                row.Cells[0].Value = i;
                i++;
            }
            return true;
        }

        private void cmbCategory1_SelectedValueChanged(object sender, EventArgs e)
        {
            

            if (cmbCategory1.Items.Count == 0) cmbCategory1.DataSource = cat.GetAllCategory1().Tables[0];
            else
            {
                 //MessageBox.Show("kat 1");
                
                if (cmbCategory1.SelectedValue.ToString() == "1")
                {
                 //       MessageBox.Show("kat 1.1");
                    cmbCategory2.DataSource = cat.GetAllCategory2("").Tables[0];
                    cmbCategory3.DataSource = cat.GetAllCategory3("", "").Tables[0];
                }
                else 
                {
                   //   MessageBox.Show("kat 1.2");
                    cmbCategory2.DataSource = cat.GetAllCategory2(cmbCategory1.SelectedValue.ToString()).Tables[0];
                    cmbCategory3.DataSource = cat.GetAllCategory3(cmbCategory1.SelectedValue.ToString(), "").Tables[0];
                }
            }
                  
                  

            cmbProductList.DisplayMember = "Name";
            cmbProductList.ValueMember = "ID";
            

        }

        private void cmbCategory2_SelectedValueChanged(object sender, EventArgs e)
        {

            if (cmbCategory2.Items.Count == 0) cmbCategory2.DataSource = cat.GetAllCategory2("").Tables[0];
            else
            {
                 //MessageBox.Show("kat 2");
                if (cmbCategory2.SelectedValue.ToString() == "1")
                {
                    //    MessageBox.Show("kat 2.1");
                    cmbCategory3.DataSource = cat.GetAllCategory3("", "").Tables[0];
                }
                else                     
                {
                    //   MessageBox.Show("kat 2.2");
                    //    MessageBox.Show(cmbCategory2.SelectedValue.ToString());
                    cmbCategory3.DataSource = cat.GetAllCategory3("", cmbCategory2.SelectedValue.ToString()).Tables[0];
                }
            }
            
        }

        private void cmbCategory3_SelectedValueChanged(object sender, EventArgs e)
        {
            
            if (cmbCategory3.Items.Count == 0) cmbCategory3.DataSource = cat.GetAllCategory3("", "").Tables[0];
            if (cmbCategory2.SelectedValue.ToString()!="System.Data.DataRowView")
                if (cmbCategory3.SelectedValue.ToString() != "System.Data.DataRowView")
                {

                    table = prod.GetProductFromDB(cmbCategory1.SelectedValue.ToString(), cmbCategory2.SelectedValue.ToString(), cmbCategory3.SelectedValue.ToString());
                    cmbProductList.Text = "";
                    cmbProductList.DataSource = table.Tables[0];
                    cmbProductList.Update();

                    //MessageBox.Show(cmbCategory1.SelectedValue.ToString() + " + " + cmbCategory2.SelectedValue.ToString() + " + " + cmbCategory3.SelectedValue.ToString());

           
                }
            
        }

        private void cmbProductList_SelectedValueChanged(object sender, EventArgs e)
        {
            DataSet table = new DataSet();
            table = prod.GetProductFromDBAsDataSet(Convert.ToInt32(cmbProductList.SelectedValue));
            if (table.Tables[0].Rows.Count > 0)
            {
                txtPrice.Text = table.Tables[0].Rows[0].ItemArray[6].ToString();
                NomPrice = Convert.ToDecimal(table.Tables[0].Rows[0].ItemArray[6].ToString());
                
                //MessageBox.Show(Convert.ToDouble(txtPrice.Text).ToString());
                lbUnits.Text = table.Tables[0].Rows[0].ItemArray[11].ToString();
            }
        }

        
        

        private void btnCorrect_Click(object sender, EventArgs e)
        {
            if (NomPrice > 0)
            {
                //MessageBox.Show(NomPrice.ToString());
                try
                {
                    txtPrice.Text = (((100 + (Convert.ToDecimal(txtCorection.Text))) * NomPrice) / 100).ToString();
                }
                catch
                {
                }
            }
        }

        private void btnCalculateTotalPrice_Click(object sender, EventArgs e)
        {
            Decimal TotalPrice = 0;
            Decimal TotalPriseAndVAT = 0;
            foreach (DataGridViewRow row in dgvProductsList.Rows)
            {
                TotalPrice += Convert.ToDecimal(row.Cells[7].Value);
                TotalPriseAndVAT += Convert.ToDecimal(row.Cells[7].Value) * ((Convert.ToDecimal(txtVAT.Text) + 100) / 100);
            }

            txtTotalPrice.Text = TotalPrice.ToString();
            txtTotalAndVAT.Text = TotalPriseAndVAT.ToString();

        }

        private void btnEditRemove_Click(object sender, EventArgs e)
        {

            if (dgvProductsList.SelectedRows.Count != 0)
            {
                cmbProductList.SelectedValue = dgvProductsList.SelectedRows[0].Cells[8].Value;
                txtPrice.Text = dgvProductsList.SelectedRows[0].Cells[5].Value.ToString();
                txtQuantity.Text = dgvProductsList.SelectedRows[0].Cells[6].Value.ToString();
                txtCorection.Text = "0";
                foreach (DataGridViewRow row in dgvProductsList.SelectedRows)
                {
                    dgvProductsList.Rows.Remove(row);
                }

                Int32 i = 1;
                foreach (DataGridViewRow row in dgvProductsList.Rows)
                {
                    row.Cells[0].Value = i;
                    i++;
                }
            }
        }

        public void FillOldOffer(DataGridViewRow parameters)
        {
            DataSet table = new DataSet();
            MySqlDataAdapter MyDA = new MySqlDataAdapter();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;


            MySqlConnection mysqlCon = new MySqlConnection(connString);
            mysqlCon.Open();


            string sqlSelectAll = "SELECT offered_products.*,SUM(warehouse.Quantity) FROM offered_products LEFT JOIN warehouse ON warehouse.Product_ID=offered_products.ProductID WHERE offered_products.OfferID='" + parameters.Cells[8].Value.ToString() + "' GROUP BY offered_products.ProductID";
            MyDA.SelectCommand = new MySqlCommand(sqlSelectAll, mysqlCon);
            MyDA.Fill(table);

            foreach (DataRow row in table.Tables[0].Rows)
            {
                object[] product = new object[9];
                //ProductCounter = dgvProductsList.Rows.Count + 1;
                product[0] = row.ItemArray[1];
                product[1] = prod.GetProductFromDBAsDataSet(Convert.ToInt32(row.ItemArray[2])).Tables[0].Rows[0].ItemArray[5];
                product[2] = prod.GetProductFromDBAsDataSet(Convert.ToInt32(row.ItemArray[2])).Tables[0].Rows[0].ItemArray[4];
                product[3] = row.ItemArray[6];
                product[4] = row.ItemArray[8];
                product[5] = row.ItemArray[4];
                product[6] = row.ItemArray[3];
                product[7] = row.ItemArray[7];
                product[8] = row.ItemArray[2];
               // dgvProductsList.Rows.Add(product);

                Color RowColor = new Color();
                try
                {
                    if (Convert.ToInt32(row.ItemArray[3]) > Convert.ToInt32(row.ItemArray[9]))
                        RowColor = Color.Red;
                    else
                        RowColor = Color.Black;
                }
                catch
                {
                    RowColor = Color.Cyan;
                }

                dgvProductsList.Rows.Add(product);
                dgvProductsList.Rows[dgvProductsList.RowCount - 1].DefaultCellStyle.ForeColor = RowColor;
            }
        }

        public void FillResponsibels()
        {
            DataTable table = users.GetAllUserNameAndSurname();
            foreach (DataRow row in table.Rows)
            {
                cmbResponsible.Items.Add(row.ItemArray[0].ToString());
                cmbResponsible.Items.Remove("Администратор ");
                cmbResponsible.SelectedItem = ActiveUser.GetName() + " " + ActiveUser.GetSurname();
            }
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            Add();
        }

        private void ToStPrint_Click(object sender, EventArgs e)
        {
            btnCalculateTotalPrice_Click(sender, e);


            NewOffer.Status = "Пратена";
            NewOffer.Price = Convert.ToDecimal(txtTotalPrice.Text);
            NewOffer.VATProc = Convert.ToDecimal(txtVAT.Text);
            NewOffer.PriceWithVAT = Convert.ToDecimal(txtTotalAndVAT.Text);
            NewOffer.VAT = (Convert.ToDecimal(txtTotalPrice.Text) * ((Convert.ToDecimal(txtVAT.Text)) / 100));
            NewOffer.PriceWithVAT = Convert.ToDecimal(txtTotalAndVAT.Text);
            NewOffer.Description = txtDescription.Text;
            NewOffer.FinishDate = txtDeliveryDay.Text;
            NewOffer.ValidDate = txtValid.Text;
            NewOffer.Payment = txtPaymentMethod.Text;
            NewOffer.Guarantee = txtGuarante.Text;
            NewOffer.Request = txtRequest.Text;
            NewOffer.PartnerName = txtPartner.Text;
            NewOffer.Responsible = cmbResponsible.SelectedItem.ToString();
            NewOffer.Note = txtNote.Text.ToString();

            string connString = ConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString;

            MySqlConnection mysqlCon = new MySqlConnection(connString);

            mysqlCon.Open();

            if (Owner)
            {
                if (!New)
                {
                    NewOffer.DeleteOffer();
                }
                NewOffer.AddOfferInDB();
                foreach (DataGridViewRow row in dgvProductsList.Rows)
                {

                    string sqlInsertProduct = "INSERT INTO offered_products VALUES (DEFAULT,'" +
                                                Convert.ToInt32(row.Cells[0].Value) + "','" +
                                                Convert.ToInt32(row.Cells[8].Value) + "','" +
                                                Convert.ToInt32(row.Cells[6].Value) + "',@Price," + NewOffer.OfferNo + ",'" +
                                                row.Cells[3].Value.ToString() + "',@TotalPrice,'" +
                                                row.Cells[4].Value.ToString() + "')";
                    MySqlCommand cmd = new MySqlCommand(sqlInsertProduct, mysqlCon);

                    cmd.Parameters.Add("@Price", MySqlDbType.Double, 50).Value = Convert.ToDecimal(row.Cells[5].Value);
                    cmd.Parameters.Add("@TotalPrice", MySqlDbType.Double, 50).Value = Convert.ToDecimal(row.Cells[7].Value);
                    cmd.ExecuteNonQuery();


                }
            }

            mysqlCon.Close();

            DataGridViewRow parametars = new DataGridViewRow();
            DataGridView dgvtmp = new DataGridView();

            dgvtmp.ColumnCount = 14;
            dgvtmp.RowCount = 1;
            dgvtmp.Rows[0].Cells[0].Value = NewOffer.OfferNo.ToString() + "-" + (Convert.ToDateTime(NewOffer.Date).ToString("yy"));
            dgvtmp.Rows[0].Cells[1].Value = txtPartner.Text;
            dgvtmp.Rows[0].Cells[2].Value = (Convert.ToDecimal(txtTotalPrice.Text)).ToString("n");
            dgvtmp.Rows[0].Cells[3].Value = ((Convert.ToDecimal(txtTotalPrice.Text) * ((Convert.ToDecimal(txtVAT.Text)) / 100))).ToString("n");
            dgvtmp.Rows[0].Cells[4].Value = (Convert.ToDecimal(txtTotalAndVAT.Text)).ToString("n");
            dgvtmp.Rows[0].Cells[5].Value = (Convert.ToDateTime(NewOffer.Date)).ToString("dd/MM/yyyy");
            dgvtmp.Rows[0].Cells[6].Value = txtDeliveryDay.Text;
            dgvtmp.Rows[0].Cells[7].Value = txtValid.Text;
            dgvtmp.Rows[0].Cells[8].Value = txtPaymentMethod.Text;
            if (txtGuarante.Text != "")
                dgvtmp.Rows[0].Cells[9].Value = "Гаранциски услови: " + txtGuarante.Text;
            else
                dgvtmp.Rows[0].Cells[9].Value = " ";

            dgvtmp.Rows[0].Cells[10].Value = NewOffer.Responsible;
            dgvtmp.Rows[0].Cells[11].Value = txtDescription.Text;
            dgvtmp.Rows[0].Cells[12].Value = txtRequest.Text;
            dgvtmp.Rows[0].Cells[13].Value = txtNote.Text;
            parametars = dgvtmp.Rows[0];




            Program.GetMainForm.ShowControl(ControlsEnum.PRINT_OFFER_CONTROL, parametars);
            this.Dispose();
        }

        private void ToStBack_Click(object sender, EventArgs e)
        {
            if (Program.GetMainForm.Back())
                this.Dispose();
        }


        private void ToStForward_Click(object sender, EventArgs e)
        {
            if (Program.GetMainForm.Forward())
                this.Dispose();
        }

        private void txtNote_TextChanged(object sender, EventArgs e)
        {
            if (txtNote.Text.Length > 500)
            {
                txtNote.Text=txtNote.Text.Substring(0,txtNote.Text.Length-1);
                MessageBox.Show("Не може да внесете повеќе од 500 карактери во забелешка");
            }
        }

        
        

        


    }
}
